<?php
require 'lib.php';
$server = "172.21.147.31";
$connectionInfo = array( "Database"=>"ss2g6", "UID"=>"ss2g6", "PWD"=>"group6", 'ReturnDatesAsStrings'=> true );
$conn = sqlsrv_connect( $server, $connectionInfo );

if ($conn == false){
	 die( print_r( sqlsrv_errors(), true));
}

$params = array();
$options =  array( "Scrollable" => SQLSRV_CURSOR_KEYSET );

if(isset($_GET['update'])){
    $updateCompanyName = $_GET['companyName'];
    $updatePharmacyName = $_GET['pharmacyName'];
    $updateSupervisorId = $_GET['supervisorId'];
    $updateStartDate = $_GET['startDate'];
    $updateEndDate = $_GET['endDate'];

    $sql = "SELECT * FROM Contract WHERE companyName='$updateCompanyName' AND pharmacyName='$updatePharmacyName' AND supervisorId=$updateSupervisorId AND startDate='$updateStartDate' AND endDate='$updateEndDate'";
    $result = sqlsrv_query($conn, $sql , $params, $options );
    $row = sqlsrv_fetch_array($result, SQLSRV_FETCH_ASSOC);

    $updateCompanyName = $row['companyName'];
    $updatePharmacyName = $row['pharmacyName'];
    $updateSupervisorId = $row['supervisorId'];
    $updateStartDate = $row['startDate'];
    $updateEndDate = $row['endDate'];
    $updateText = $row['text'];
}

if(isset($_POST['Update'])){
    $updateCompanyNameOld = $_POST['companyNameOld'];
    $updatePharmacyNameOld = $_POST['pharmacyNameOld'];
    $updateSupervisorIdOld = $_POST['supervisorIdOld'];
    $updateStartDateOld = $_POST['startDateOld'];
    $updateEndDateOld = $_POST['endDateOld'];
    
    $updateCompanyName = $_POST['companyName'];
    $updatePharmacyName = $_POST['pharmacyName'];
    $updateSupervisorId = $_POST['supervisorId'];
    $updateStartDate = $_POST['startDate'];
    $updateEndDate = $_POST['endDate'];
    $updateText = $_POST['text'];

    $sql = "UPDATE Contract SET companyName='$updateCompanyName', pharmacyName='$updatePharmacyName', supervisorId=$updateSupervisorId, startDate='$updateStartDate', endDate='$updateEndDate', text='$updateText'"; 
    $sql = $sql . " WHERE companyName='$updateCompanyNameOld' AND pharmacyName='$updatePharmacyNameOld' AND supervisorId=$updateSupervisorIdOld AND startDate='$updateStartDateOld' AND endDate='$updateEndDateOld'";
    $result = sqlsrv_query($conn, $sql , $params, $options );
    if ($result === false){
        if(($errors = sqlsrv_errors()) != null){
            $error_msg = "";
            foreach($errors as $error){
                $error_msg = $error_msg . " " . cut_string_using_last(']', $error['message'], 'right', false);
            }
        }
    }else{
        $confirm = "Contract updated.";
    }
}

if(isset($_POST['Insert'])) {
    $companyName = $_POST['companyName'];
    $pharmacyName = $_POST['pharmacyName'];
    $supervisorId = $_POST['supervisorid'];
    $text = $_POST['text'];
    $startDate = $_POST['startDate'];
    $endDate = $_POST['endDate'];
	if($text == "" || $startDate == "" || $endDate == ""){
		$validation_error = true;
	}
	else {
		$validation_error = false;
	}
	
    $sql = "INSERT INTO Contract VALUES ('$companyName', '$pharmacyName', '$supervisorId', '$text', '$startDate', '$endDate')";
	
	if($validation_error == false ){
		$result = sqlsrv_query( $conn, $sql, $params, $options );

        if ($result === false){
            if(($errors = sqlsrv_errors()) != null){
                $error_msg = "";
                foreach($errors as $error){
                    $error_msg = $error_msg . " " . cut_string_using_last(']', $error['message'], 'right', false);
                }
            }
        }
        else{
            $confirm = "The contract is inserted.";
        }
    }
}

if(isset($_GET['delete'])){
	$companyName = $_GET['companyName'];
	$pharmacyName = $_GET['pharmacyName'];
    $supervisorId = $_GET['supervisorId'];
    $text = $_GET['text'];
    $startDate = $_GET['startDate'];
    $endDate = $_GET['endDate'];
	$sql = "DELETE FROM Contract WHERE Companyname = '". $companyName . "' AND pharmacyName='".$pharmacyName."' AND supervisorId='".$supervisorId. "' AND startDate='".$startDate. "' AND endDate='".$endDate. "'";
	$params = array();
    $options =  array( "Scrollable" => SQLSRV_CURSOR_KEYSET );
    $result = sqlsrv_query( $conn, $sql, $params, $options );

    if ($result === false){
        if(($errors = sqlsrv_errors()) != null){
            $error_msg = "";
            foreach($errors as $error){
                $error_msg = $error_msg . " " . cut_string_using_last(']', $error['message'], 'right', false);
            }
        }
    }
	else{
		$error = false;
	}
}

if (isset($_POST['submit'])){
    $sql = $_POST['query'];
}
else{
    $sql = "SELECT * FROM Contract";
}
    $params = array();
    $options =  array( "Scrollable" => SQLSRV_CURSOR_KEYSET );
    $stmt = sqlsrv_query( $conn, $sql , $params, $options );
?>


<? include 'html_head.php' ?>
    <? include 'header.php' ?>

    <div class="container-fluid">
        <div class="row-fluid">
            <div class="span2">
                <? include 'sidebar.php' ?>
            </div><!--/span-->
            <div class="span9">
			
			<? 
			if(isset($_GET['delete'])){
				if (isset($error_msg)){ ?>
					<div class="alert alert-error">
						<? echo $error_msg;?>
					</div>
					
				<? 
				} 
				else { ?>
					<div class="alert alert-success">
					<? echo "Data deleted successfully."?>
					</div>
					
				<?
				} 
			}?>

            <? if (isset($_POST['Update'])){
                    if (isset($error_msg)){ ?>
					<div class="alert alert-error">
					<? echo $error_msg;?>
					</div>
            <? }else{ ?>
					<div class="alert alert-success">
					<? echo $confirm;?>
					</div>
            <? }
            } ?> 
           <?
			if(isset($_POST['Insert'])){
				if($validation_error){ ?>
					<div class="alert alert-error">
						<? echo "None of the text fields can be empty.<br />";?>
					</div>
				<?
				}
				elseif (isset($error_msg)){ ?>
					<div class="alert alert-error">
						<? echo $error_msg; ?>
					</div>
					
				<? 
				} 
				else { ?>
					<div class="alert alert-success">
					<? echo $confirm;?>
					</div>
					
				<? } 
            }?>
            <?
            
		    if(sqlsrv_has_rows($stmt)){		
			    $row_count = sqlsrv_num_rows($stmt);
                $colnames = sqlsrv_field_metadata($stmt)
		    ?>
		    <table class="table table-striped table table-condensed">
		    <thead>
			    <tr>
                <?
                     echo "<th>Company Name</th>";
					echo "<th>Pharmacy Name</th>";
					echo "<th>Supervisor Id</th>";
					echo "<th>Text</th>";
					echo "<th>Start Date</th>";
					echo "<th>End Date</th>";
                ?>
			    </tr>	
			</thead>
		    <tbody>
		    <?
			    while( $row = sqlsrv_fetch_array( $stmt, SQLSRV_FETCH_ASSOC)){
		    ?>
			    <tr>
				<?
					foreach ($colnames as &$colname){
					$cellname = $colname['Name'];
					echo "<td>";
					if ($colname['Type'] == 93){
						$temp_date = explode(" ", $row[$cellname]);
						echo $temp_date[0];
					}
					else{
						echo $row[$cellname];
					}
					echo "</td>";
					}
					$companyName = urlencode($row['companyName']);
                    $pharmacyName = urlencode($row['pharmacyName']);
                    $supervisorId = urlencode($row['supervisorId']);
                    $text = urlencode($row['text']);
                    $startDate = urlencode($row['startDate']);
                    $endDate = urlencode($row['endDate']);
					echo "<td><a href='contract.php?delete=true&startDate=".$startDate. "&endDate=".$endDate. "&text=" .$text. "&companyName=" . $companyName ."&pharmacyName=" . $pharmacyName . "&supervisorId=" . $supervisorId . "'>Delete</a></td>";
					echo "<td><a href='contract.php?update=true&startDate=".$startDate. "&endDate=".$endDate. "&text=" .$text. "&companyName=" . $companyName ."&pharmacyName=" . $pharmacyName . "&supervisorId=" . $supervisorId . "#updates'>Update</a></td>";
				
				?>
			    </tr>
		        <? } ?>
		    </tbody>
		</table>
		<?
		}
		else{
			echo "<h2> No records. </h2>";
		}
		?>
          <div class="row-fluid">
            <div class="span12">

            <? if (isset($_GET['update'])){ ?>
            <div id="updates">
                <h3>Update Contract information:</h3> 
                    <form method="post" action="contract.php" class="well form-horizontal">
                    <input type="hidden" name="companyNameOld" value="<? echo $updateCompanyName; ?>" />
                    <input type="hidden" name="pharmacyNameOld" value="<? echo $updatePharmacyName; ?>" />
                    <input type="hidden" name="supervisorIdOld" value="<? echo $updateSupervisorId; ?>" />
                    <input type="hidden" name="startDateOld" value="<? echo $updateStartDate; ?>" />
                    <input type="hidden" name="endDateOld" value="<? echo $updateEndDate; ?>" />
                    <div class="control-group">
                        <label class="control-label">Company Name</label>
                        <div class="controls">
                        	<? 
								$sql2 = "SELECT companyName FROM PharmaceuticalCompany order by companyName ASC";
								$result = sqlsrv_query($conn,$sql2);
								echo "<select name = 'companyName'>";
								while ($data=sqlsrv_fetch_array($result, SQLSRV_FETCH_ASSOC)){
									echo "<option";
                                    if ($data['companyName'] == $updateCompanyName){
                                        echo " selected=selected";
                                    }
                                    echo ">";
									echo $data['companyName']; 
									echo "</option>";
								}
								echo "</select>";
							?>
                            <!--<input type="text" name="companyName" id="companyName" placeholder="eg. Alibaba Medical Centre" />-->
                            <span class="help-inline"><a href="pharmaceutical.php" target="_blank">Create new Pharmaceutical Company</a></span>
                        </div>
                    </div>
                    <div class="control-group">
                        <label class="control-label">Pharmacy Name</label>
                        <div class="controls">
                        	<? 
								$sql2 = "SELECT pharmacyName FROM Pharmacy order by pharmacyName ASC";
								$result = sqlsrv_query($conn,$sql2);
								echo "<select name = 'pharmacyName'>";
								while ($data=sqlsrv_fetch_array($result, SQLSRV_FETCH_ASSOC)){
									echo "<option";
                                    if ($data['pharmacyName'] == $updatePharmacyName){
                                        echo " selected=selected";
                                    }
                                    echo ">";
									echo $data['pharmacyName']; 
									echo "</option>";
								}
								echo "</select>";
							?>
                            <!--<input type="text" name="pharmacyName" id="pharmacyName" placeholder="eg. Smith Pharmacy" />-->
                            <span class="help-inline"><a href="pharmacy.php" target="_blank">Create new Pharmacy</a></span>
                        </div> 
                    </div>
                    <div class="control-group">
                        <label class="control-label">Supervisor ID</label>
                        <div class="controls">
                        	<? 
								$sql2 = "SELECT supervisorId FROM Supervisor order by supervisorId ASC";
								$result = sqlsrv_query($conn,$sql2);
								echo "<select name = 'supervisorId'>";
								while ($data=sqlsrv_fetch_array($result, SQLSRV_FETCH_ASSOC)){
									echo "<option";
                                    if ($data['supervisorId'] == $updateSupervisorId){
                                        echo " selected=selected";
                                    }
                                    echo ">";
									echo $data['supervisorId']; 
									echo "</option>";
								}
								echo "</select>";
							?>
                            <!--<input type="text" name="supervisorid" id="supervisorid" placeholder="ID of the supervisor" />-->
                            <span class="help-inline"><a href="supervisor.php" target="_blank">Create new Supervisor</a></span>
                        </div>
                    </div>
                    <div class="control-group">
                        <label class="control-label">Start Date</label>
                        <div class="controls">
                        <input type="text" name="startDate" id="startDate" value="<? echo $updateStartDate; ?>" />
                        </div>
                    </div>
                    <div class="control-group">
                        <label class="control-label">End Date</label>
                        <div class="controls">
                        <input type="text" name="endDate" id="endDate" value="<? echo $updateEndDate ?>"/>
                        </div>
                    </div>
                    <div class="control-group">
                        <label class="control-label">Content</label>
                        <div class="controls"> 
                        <textarea class="span5" name="text" id="text"><? echo $updateText ?></textarea>
                        </div>
                    </div>
                    <div class="form-actions">
                        <input type="submit" name="Update" value="Update"  class="btn btn-primary"/>
                    </div> 
                    </form>
 
            </div>
            <? } ?>

            <h3>Create a new Contract information:</h3> 
                    <form method="post" action="contract.php" class="well form-horizontal">
                    <div class="control-group">
                        <label class="control-label">Company Name</label>
                        <div class="controls">
                        	<? 
								$sql2 = "SELECT companyName FROM PharmaceuticalCompany order by companyName ASC";
								$result = sqlsrv_query($conn,$sql2);
								echo "<select name = 'companyName'>";
								while ($data=sqlsrv_fetch_array($result, SQLSRV_FETCH_ASSOC)){
									echo "<option>";
									echo $data['companyName']; 
									echo "</option>";
								}
								echo "</select>";
							?>
                            <!--<input type="text" name="companyName" id="companyName" placeholder="eg. Alibaba Medical Centre" />-->
                            <span class="help-inline"><a href="pharmaceutical.php" target="_blank">Create new Pharmaceutical Company</a></span>
                        </div>
                    </div>
                    <div class="control-group">
                        <label class="control-label">Pharmacy Name</label>
                        <div class="controls">
                        	<? 
								$sql2 = "SELECT pharmacyName FROM Pharmacy order by pharmacyName ASC";
								$result = sqlsrv_query($conn,$sql2);
								echo "<select name = 'pharmacyName'>";
								while ($data=sqlsrv_fetch_array($result, SQLSRV_FETCH_ASSOC)){
									echo "<option>";
									echo $data['pharmacyName']; 
									echo "</option>";
								}
								echo "</select>";
							?>
                            <!--<input type="text" name="pharmacyName" id="pharmacyName" placeholder="eg. Smith Pharmacy" />-->
                            <span class="help-inline"><a href="pharmacy.php" target="_blank">Create new Pharmacy</a></span>
                        </div> 
                    </div>
                    <div class="control-group">
                        <label class="control-label">Supervisor ID</label>
                        <div class="controls">
                        	<? 
								$sql2 = "SELECT supervisorId FROM Supervisor order by supervisorId ASC";
								$result = sqlsrv_query($conn,$sql2);
								echo "<select name = 'supervisorid'>";
								while ($data=sqlsrv_fetch_array($result, SQLSRV_FETCH_ASSOC)){
									echo "<option>";
									echo $data['supervisorId']; 
									echo "</option>";
								}
								echo "</select>";
							?>
                            <!--<input type="text" name="supervisorid" id="supervisorid" placeholder="ID of the supervisor" />-->
                            <span class="help-inline"><a href="supervisor.php" target="_blank">Create new Supervisor</a></span>
                        </div>
                    </div>
                    <div class="control-group">
                        <label class="control-label">Start Date</label>
                        <div class="controls">
                            <input type="text" name="startDate" id="startDate" placeholder="eg. 2012-03-20" />
                        </div>
                    </div>
                    <div class="control-group">
                        <label class="control-label">End Date</label>
                        <div class="controls">
                            <input type="text" name="endDate" id="endDate" placeholder="eg. 2012-03-20"/>
                        </div>
                    </div>
                    <div class="control-group">
                        <label class="control-label">Content</label>
                        <div class="controls"> 
                            <textarea class="span5" name="text" id="text" placeholder="Details of the Contract"></textarea>
                        </div>
                    </div>
                    <div class="form-actions">
                        <input type="submit" name="Insert" value="Insert"  class="btn btn-primary"/>
                    </div> 
                    </form>
                    <h3>Enter your query:</h3>
                    <form method="post" action="custom.php" class="well form-inline">
                    <div class="control-group">
                        <div class="controls">
                            <textarea name="query" class="span10" id="textarea" rows="5" placeholder = 'SQL query'></textarea>
                        </div>
                    </div>
                    <p>
                    <p><input type="submit" name="submit" class="btn btn-primary" href="#" /></p>
                    </form>
                    
            </div><!--/span-->
          </div><!--/row-->
        </div><!--/span-->
      </div><!--/row-->

<? include 'html_tail.php'; ?>
